package cn.krui.helper.db;

import cn.krui.helper.ConfigurationBean;
import java.lang.reflect.Array;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.ibatis.builder.BuilderException;
import org.apache.ibatis.parsing.GenericTokenParser;
import org.apache.ibatis.parsing.TokenHandler;

/**
 * @author zc
 */
public class SQL {
	static {

	}

	static void auth() {
		if (sql == null) {
			sql = new SQL();
		}
	}

	static SQL sql = null;

	public static SelectSQL select(String... fields) {
		auth();
		sql.getClass();
		return (sql.new SelectSQL()).select(fields);
	}

	public static SelectSQL select() {
		auth();
		sql.getClass();
		return sql.new SelectSQL();
	}

	public static UpdateSQL update(String table) {
		auth();
		sql.getClass();
		return (sql.new UpdateSQL()).update(table);
	}

	public static InsertSQL insert(String table) {
		auth();
		sql.getClass();
		return (sql.new InsertSQL()).insert(table);
	}

	public static InsertSQL insert(String table, Map<String, Object> values) {
		auth();
		sql.getClass();
		return (sql.new InsertSQL()).insert(table).addValue(values);
	}

	public static DeleteSQL delete(String table) {
		auth();
		sql.getClass();
		return (sql.new DeleteSQL()).delete(table);
	}

	public static DeleteSQL delete(String table, String where) {
		auth();
		sql.getClass();
		return (sql.new DeleteSQL()).delete(table).where(where);
	}

	public static DeleteSQL delete(String table, String where, Map<String, Object> params) {
		auth();
		sql.getClass();
		return (sql.new DeleteSQL()).delete(table).where(where).addParameters(params);
	}

	public static ExecuteSQL exec(String procName) {
		auth();
		sql.getClass();
		return (sql.new ExecuteSQL()).exec(procName);
	}

	public static ExecuteSQL exec(String procName, Map<String, Object> params) {
		auth();
		sql.getClass();
		return (sql.new ExecuteSQL()).exec(procName).addParameters(params);
	}


	public class SQLBase {
		Map<String, Integer> complexParams = new HashMap<>();

		Map<String, Object> params = new LinkedHashMap<>();

		SQLBase parent = null;

		SQLBase getRoot() {
			SQLBase r1 = this, r2 = this.parent;
			while (r2 != null) {
				r1 = r2;
				r2 = r2.parent;
			}
			return r1;
		}

		Map<String, Integer> getRootComplexParams() {
			return (getRoot()).complexParams;
		}

		void _addParameter(String paramName, Object paramValue) {
			SQLBase b = getRoot();
			Map<String, Object> p = b.params;
			Map<String, Integer> complexParams = b.complexParams;
			if (paramValue == null) {
				p.put(paramName, null);
			} else {
				Class<?> clazz = paramValue.getClass();
				if (clazz.isArray()) {
					complexParams.put(paramName, Integer.valueOf(Array.getLength(paramValue)));
					for (int i = 0; i < Array.getLength(paramValue); i++){
						p.put("__" + paramName + "_" + i, Array.get(paramValue, i));
					}
				} else if (List.class.isAssignableFrom(clazz)) {
					List<?> li = (List) paramValue;
					complexParams.put(paramName, Integer.valueOf(li.size()));
					for (int i = 0; i < li.size(); i++){
						p.put("__" + paramName + "_" + i, li.get(i));
					}
				} else if (Set.class.isAssignableFrom(clazz)) {
					Set<?> set = (Set) paramValue;
					complexParams.put(paramName, Integer.valueOf(set.size()));
					int i = 0;
					for (Object s : set) {
						p.put("__" + paramName + "_" + i, s);
						i++;
					}
				} else if (Map.class.isAssignableFrom(clazz)) {
					Map<?, ?> m = (Map<?, ?>) paramValue;
					complexParams.put(String.valueOf(paramName) + "_key", Integer.valueOf(m.size()));
					complexParams.put(String.valueOf(paramName) + "_value", Integer.valueOf(m.size()));
					int i = 0;
					for (Object s : m.keySet()) {
						p.put("__" + paramName + "_key_" + i, s);
						i++;
					}
					i = 0;
					for (Object s : m.values()) {
						p.put("__" + paramName + "_value_" + i, s);
						i++;
					}
				} else {
					p.put(paramName, paramValue);
				}
			}
		}

		public void _addParameters(Map<String, Object> params) {
			if (params != null){
				for (Map.Entry<String, Object> p : params.entrySet()){
					_addParameter(p.getKey(), p.getValue());
				}
			}
		}

		Map<String, Object> parameter = null;

		public Map<String, Object> getParameters(boolean generateNew) {
			if (this.parameter == null || generateNew) {
				Map<String, Object> p = new LinkedHashMap<>();
				p.putAll((getRoot()).params);
				this.parameter = p;
			}
			return this.parameter;
		}

		public Map<String, Object> getParameters() {
			return getParameters(false);
		}
	}
	
	public class SelectSQL extends SQLBase {
		List<String> idFields;

		PageModel pageModel;

		List<String> fields;

		List<TableEntity> tables;

		List<JoinEntity> join;

		List<String> orderBy;

		List<String> groupBy;

		List<String> having;

		List<String> filter;

		Integer offset;

		Integer rows;

		List<UnionEntity> union;

		public Boolean inverse;

		public SelectSQL() {
			this.idFields = new ArrayList<>();
			this.pageModel = null;
			this.fields = new ArrayList<>();
			this.tables = new ArrayList<>();
			this.join = new ArrayList<>();
			this.orderBy = new ArrayList<>();
			this.groupBy = new ArrayList<>();
			this.having = new ArrayList<>();
			this.filter = new ArrayList<>();
			this.offset = null;
			this.rows = null;
			this.union = new ArrayList<>();
			this.inverse = Boolean.valueOf(false);
		}

		public SelectSQL setIdFields(String... idColumns) {
			if (idColumns != null) {
				byte b;
				int i;
				String[] arrayOfString;
				for (i = (arrayOfString = idColumns).length, b = 0; b < i;) {
					String id = arrayOfString[b];
					if (id != null && id.length() > 0){
						this.idFields.add(id);
					}
					b++;
				}
			}
			return this;
		}

		public List<String> getFields() {
			return this.fields;
		}

		public SelectSQL select(String... columns) {
			if (columns != null) {
				byte b;
				int i;
				String[] arrayOfString;
				for (i = (arrayOfString = columns).length, b = 0; b < i;) {
					String f = arrayOfString[b];
					if (f != null && f.length() > 0){
						this.fields.add(f);
					}
					b++;
				}
			}
			return this;
		}

		public SelectSQL clearColumns() {
			this.fields.clear();
			return this;
		}

		public SelectSQL clearOrderBy(){
			this.orderBy.clear();
			return this;
		}

		public SelectSQL from(String... tables) {
			if (tables != null) {
				byte b;
				int i;
				String[] arrayOfString;
				for (i = (arrayOfString = tables).length, b = 0; b < i;) {
					String t = arrayOfString[b];
					if (t != null && t.length() > 0){
						this.tables.add(new TableEntity(t, false));
					}
					b++;
				}
			}
			return this;
		}

		public SelectSQL from(TableEntity table) {
			if (table != null){
				this.tables.add(table);
			}
			return this;
		}

		public SelectSQL from(SelectSQL innerTables, String alias) {
			this.tables.add(new TableEntity(innerTables, false, " (%s) " + alias));
			addParameters(innerTables.getParameters());
			getRootComplexParams().putAll(innerTables.getRootComplexParams());
			innerTables.parent = this;
			return this;
		}

		public SelectSQL innerJoin(String table, String on) {
			if (table != null && table.length() > 0){
				this.tables.add(new TableEntity(" INNER JOIN " + table + " ON " + on, true));
			}
			return this;
		}

		public SelectSQL innerJoin(String table, String alias, String on) {
			if (table != null && table.length() > 0){
				if (alias != null && alias.length() > 0) {
					this.tables.add(new TableEntity(" INNER JOIN " + table + "  " + alias + " ON " + on, true));
				} else {
					this.tables.add(new TableEntity(" INNER JOIN " + table + " ON " + on, true));
				}
			}
			return this;
		}

		public SelectSQL innerJoin(SelectSQL subQuery, String alias, String on) {
			addParameters(subQuery.getParameters());
			getRootComplexParams().putAll(subQuery.getRootComplexParams());
			subQuery.parent = this;
			this.join.add(new JoinEntity(" INNER JOIN ", subQuery, alias, on));
			return this;
		}

		public SelectSQL leftJoin(String table, String on) {
			this.tables.add(new TableEntity(" LEFT JOIN " + table + " ON " + on, true));
			return this;
		}

		public SelectSQL leftJoin(String table, String alias, String on) {
			if (alias != null && alias.length() > 0) {
				this.tables.add(new TableEntity(" LEFT JOIN " + table + "  " + alias + " ON " + on, true));
			} else {
				this.tables.add(new TableEntity(" LEFT JOIN " + table + " ON " + on, true));
			}
			return this;
		}

		public SelectSQL leftJoin(SelectSQL subQuery, String alias, String on) {
			addParameters(subQuery.getParameters());
			getRootComplexParams().putAll(subQuery.getRootComplexParams());
			subQuery.parent = this;
			this.join.add(new JoinEntity(" LEFT JOIN ", subQuery, alias, on));
			return this;
		}

		public SelectSQL rightJoin(String table, String on) {
			if (table != null && table.length() > 0){
				this.tables.add(new TableEntity(" RIGHT JOIN " + table + " ON " + on, true));
			}
			return this;
		}

		public SelectSQL rightJoin(String table, String alias, String on) {
			if (table != null && table.length() > 0){
				if (alias != null && alias.length() > 0) {
					this.tables.add(new TableEntity(" RIGHT JOIN " + table + "  " + alias + " ON " + on, true));
				} else {
					this.tables.add(new TableEntity(" RIGHT JOIN " + table + " ON " + on, false));
				}
			}
			return this;
		}

		public SelectSQL rightJoin(SelectSQL subQuery, String alias, String on) {
			addParameters(subQuery.getParameters());
			getRootComplexParams().putAll(subQuery.getRootComplexParams());
			subQuery.parent = this;
			this.join.add(new JoinEntity(" RIGHT JOIN", subQuery, alias, on));
			return this;
		}

		public SelectSQL orderBy(String... columns) {
			if (columns != null) {
				byte b;
				int i;
				String[] arrayOfString;
				for (i = (arrayOfString = columns).length, b = 0; b < i;) {
					String c = arrayOfString[b];
					if (c != null && c.length() > 0){
						this.orderBy.add(c);
					}
					b++;
				}
			}
			return this;
		}

		public SelectSQL groupBy(String... groups) {
			if (groups != null) {
				byte b;
				int i;
				String[] arrayOfString;
				for (i = (arrayOfString = groups).length, b = 0; b < i;) {
					String g = arrayOfString[b];
					if (g != null && g.length() > 0){
						this.groupBy.add(g);
					}
					b++;
				}
			}
			return this;
		}

		public SelectSQL having(String... having) {
			if (having != null) {
				byte b;
				int i;
				String[] arrayOfString;
				for (i = (arrayOfString = having).length, b = 0; b < i;) {
					String h = arrayOfString[b];
					if (h != null && h.length() > 0){
						this.having.add(h);
					}
					b++;
				}
			}
			return this;
		}

		public SelectSQL where(String where) {
			if (where != null && where.length() > 0){
				this.filter.add(where);
			}
			return this;
		}

		public SelectSQL where(String where, Map<String, Object> params) {
			if (where != null && where.length() > 0){
				this.filter.add(where);
			}
			if (params != null){
				addParameters(params);
			}
			return this;
		}

		public SelectSQL addParameter(String paramName, Object paramValue) {
			_addParameter(paramName, paramValue);
			return this;
		}

		public SelectSQL addParameters(Map<String, Object> params) {
			_addParameters(params);
			return this;
		}

		public SelectSQL union(SelectSQL query, String type) {
			addParameters(query.getParameters());
			getRootComplexParams().putAll(query.getRootComplexParams());
			query.parent = this;
			this.union.add(new UnionEntity(type, query));
			return this;
		}

		public SelectSQL offset(Integer offset) {
			this.offset = offset;
			return this;
		}

		public SelectSQL rows(Integer rows) {
			this.rows = rows;
			return this;
		}

		public SelectSQL noPage() {
			this.offset = null;
			this.rows = null;
			return this;
		}

		public SelectSQL page(int pageNums, int pageSize) {
			this.offset = Integer.valueOf((pageNums - 1) * pageSize);
			this.rows = Integer.valueOf(pageSize);
			return this;
		}

		public String toString() {
			if (this.pageModel == null) {
				this.pageModel = ConfigurationBean.pageModel;
				if (this.pageModel == null){
					if (ConfigurationBean.isOracle) {
						this.pageModel = PageModel.OFFSET;
					} else {
						this.pageModel = PageModel.LIMIT;
					}
				}
			}
			StringBuilder sql = new StringBuilder("SELECT ");
			if (this.rows != null && this.rows.intValue() > 0){
				if (this.pageModel == PageModel.MS_ROWNUMBER) {
					if (this.offset != null && this.offset.intValue() > 0) {
						sql.append(" __kruidbhelper_data.* FROM (SELECT ");
						sql.append(" ROW_NUMBER ()  OVER(ORDER BY ");
						if (this.orderBy.isEmpty()) {
							if (this.idFields.isEmpty()) {
								sql.append("id,");
							} else {
								for (String c : this.idFields){
									sql.append(c).append(',');
								}
							}
						} else {
							for (String c : this.orderBy){
								sql.append(c).append(',');
							}
						}
						sql.deleteCharAt(sql.length() - 1);
						sql.append(')');
						sql.append("  __kruidbhelper_row_number,");
					} else {
						sql.append(" TOP ").append(this.rows).append(" ");
					}
				} else if (this.pageModel == PageModel.TOP) {
					int top = 0;
					if (this.offset != null && this.offset.intValue() > 0) {
						top = this.offset.intValue();
						this.inverse = Boolean.valueOf(true);
						sql.append(" TOP ").append(this.rows).append(" ");
						sql.append(" __kruidbhelper_data.* FROM (SELECT ");
					}
					if (this.rows != null && this.rows.intValue() > 0){
						top += this.rows.intValue();
					}
					sql.append(" TOP ").append(top).append(" ");
				}
			}
			if (this.fields.isEmpty()) {
				sql.append(" * ");
			} else {
				for (String f : this.fields){
					sql.append(f).append(',');
				}
				sql.deleteCharAt(sql.length() - 1);
			}
			sql.append(" FROM  ");
			if (!this.tables.isEmpty()) {
				for (TableEntity t : this.tables) {
					if (t.isComplex.booleanValue()) {
						sql.deleteCharAt(sql.length() - 1);
						sql.append(t.toString()).append(' ');
						continue;
					}
					sql.append(t.toString()).append(',');
				}
				sql.deleteCharAt(sql.length() - 1);
			}
			if (!this.join.isEmpty()) {
				for (JoinEntity j : this.join){
					sql.append(j.jointype).append(" (").append(j.entity.toString()).append(")  ").append(j.alias)
							.append(" ON ").append(j.on).append(",");
				}
				sql.deleteCharAt(sql.length() - 1);
			}
			int first = 0;
			if (this.offset != null && this.offset.intValue() > 0){
				first = this.offset.intValue();
			}
			int end = 0;
			if (this.rows != null && this.rows.intValue() > 0){
				end = first + this.rows.intValue() + 1;
			}
			boolean hasWhere = false;
			if (this.rows != null && this.rows.intValue() > 0 && this.pageModel == PageModel.ORACLE_ROWNUM) {
				sql.append(" WHERE (ROWNUM > ").append(first).append(" AND ROWNUM < ").append(end).append(") ");
				hasWhere = true;
			}
			if (!this.filter.isEmpty()) {
				String logicOpt = "AND";
				for (String w : this.filter) {
					String t = w.toUpperCase();
					if (t.equals("AND") || t.equals("OR")) {
						logicOpt = t;
						continue;
					}
					if (hasWhere){
						sql.append(" ").append(logicOpt).append(" ");
					}
					if (!hasWhere) {
						sql.append(" WHERE ");
						hasWhere = true;
					}
					sql.append(w);
				}
			}
			if (!this.groupBy.isEmpty()) {
				sql.append(" GROUP BY ");
				for (String g : this.groupBy){
					sql.append(g).append(',');
				}
				sql.deleteCharAt(sql.length() - 1);
				if (!this.having.isEmpty()) {
					sql.append(" HAVING ");
					for (String h : this.having){
						sql.append(h).append(" AND ");
					}
					sql.delete(sql.length() - 3, sql.length());
				}
			}
			if (!this.orderBy.isEmpty()) {
				sql.append(" ORDER BY ");
				for (String c : this.orderBy){
					sql.append(c).append(',');
				}
				sql.deleteCharAt(sql.length() - 1);
			}
			if (this.rows != null && this.rows.intValue() > 0){
				switch (this.pageModel) {
					case LIMIT:
						sql.append(" LIMIT ");
						if (this.offset != null && this.offset.intValue() > 0){
							sql.append(this.offset).append(',');
						}
						sql.append(this.rows);
						break;
					case LIMTOFFSET:
						sql.append(" LIMIT ").append(this.rows).append(" OFFSET ")
								.append((this.offset.intValue() > 0) ? this.offset.toString() : "0");
						break;
					case OFFSET:
						sql.append(" OFFSET ")
								.append((this.offset != null && this.offset.intValue() > 0) ? this.offset.toString() : "0")
								.append(" ROWS ");
						sql.append(" FETCH NEXT ").append(this.rows).append(" ROWS ONLY ");
						break;
					case MS_ROWNUMBER:
						if (this.offset != null && this.offset.intValue() > 0) {
							sql.append(")   __kruidbhelper_data ");
							sql.append(" WHERE __kruidbhelper_data.__kruidbhelper_row_number > ").append(first)
									.append(" AND __kruidbhelper_data.__kruidbhelper_row_number < ").append(end);
						}
					case TOP:
						if (this.offset != null && this.offset.intValue() > 0){
							sql.append(")  __kruidbhelper_data ");
						}
						break;
					}
			}
			if (!this.union.isEmpty()){
				for (UnionEntity u : this.union){
					sql.append(" ").append(u.unionType).append(" ").append(u.entity.toString());
				}
			}
			String temp = sql.toString();
			Map<String, Integer> cp = getRootComplexParams();
			SQL.ComplexTokenHandler handler1 = new SQL.ComplexTokenHandler("#{", "}", cp);
			GenericTokenParser parser1 = new GenericTokenParser("#{", "}", handler1);
			temp = parser1.parse(temp);
			SQL.ComplexTokenHandler handler2 = new SQL.ComplexTokenHandler("${", "}", cp);
			GenericTokenParser parser2 = new GenericTokenParser("${", "}", handler2);
			return parser2.parse(temp);
		}

		public class JoinEntity {
			public String jointype;

			public SQL.SelectSQL entity;

			public String alias;

			public String on;

			public JoinEntity(String jointype, SQL.SelectSQL entity, String alias, String on) {
				this.jointype = jointype;
				this.entity = entity;
				this.alias = alias;
				this.on = on;
			}
		}

		public class UnionEntity {
			public String unionType;

			public SQL.SelectSQL entity;

			public UnionEntity(String unionType, SQL.SelectSQL entity) {
				this.unionType = unionType;
				this.entity = entity;
			}
		}

		public class TableEntity {
			public Object table;

			public Boolean isComplex;

			public String formatter;

			public TableEntity(Object table, boolean isComplex) {
				this.table = table;
				this.isComplex = Boolean.valueOf(isComplex);
			}

			public TableEntity(Object table, boolean isComplex, String fmt) {
				this.table = table;
				this.isComplex = Boolean.valueOf(isComplex);
				this.formatter = fmt;
			}

			public String toString() {
				if (this.formatter == null || this.formatter.length() == 0){
					return this.table.toString();
				}
				return String.format(this.formatter, new Object[] { this.table });
			}
		}
	}

	public class UpdateSQL extends SQLBase {
		String tableName;

		Map<String, Object> fields;

		List<String> where;

		List<SQL.SelectSQL> subQuery;

		List<String> idFields;

		public UpdateSQL() {
			this.fields = new LinkedHashMap<>();
			this.where = new ArrayList<>();
			this.subQuery = new ArrayList<>();
			this.idFields = new ArrayList<>();
		}

		public String getIdFields() {
			StringBuilder ids = new StringBuilder();
			for (String id : this.idFields){
				ids.append(id).append(',');
			}
			if (ids.length() > 0) {
				ids.setLength(ids.length() - 1);
				return ids.toString();
			}
			return "";
		}

		public UpdateSQL setIdFields(String... idColumns) {
			if (idColumns != null) {
				byte b;
				int i;
				String[] arrayOfString;
				for (i = (arrayOfString = idColumns).length, b = 0; b < i;) {
					String id = arrayOfString[b];
					if (id != null && id.length() > 0){
						this.idFields.add(id);
					}
					b++;
				}
			}
			return this;
		}

		public UpdateSQL update(String tableName, Map<String, Object> fields, String where) {
			this.tableName = tableName;
			if (fields != null){
				addFields(fields);
			}
			return this;
		}

		public UpdateSQL update(String tableName) {
			this.tableName = tableName;
			return this;
		}

		public UpdateSQL table(String tableName) {
			this.tableName = tableName;
			return this;
		}

		public UpdateSQL addFields(Map<String, Object> fields) {
			if (fields != null){
				for (Map.Entry<String, Object> field : fields.entrySet()){
					addField(field.getKey(), field.getValue());
				}
			}
			return this;
		}

		public UpdateSQL addField(String columnExpression) {
			if (columnExpression != null && columnExpression.length() > 0){
				this.fields.put(columnExpression, "");
			}
			return this;
		}

		public UpdateSQL addField(String columnName, Object value) {
			if (value == null) {
				this.fields.put(columnName, " = NULL");
			} else if (value instanceof SQL.SelectSQL || value instanceof SQLPart || value instanceof Parameter) {
				this.fields.put(String.valueOf(columnName) + " = ", value);
				if (value instanceof SQL.SelectSQL) {
					SQL.SelectSQL ss = (SQL.SelectSQL) value;
					getRootComplexParams().putAll(ss.getRootComplexParams());
					addParameters(ss.getParameters());
					ss.parent = this;
				} else if (value instanceof Parameter) {
					Parameter p = (Parameter) value;
					addParameter(p.parameterName, p.parameterValue);
				}
			} else {
				this.fields.put(columnName, " = #{" + columnName + "}");
				addParameter(columnName, value);
			}
			return this;
		}

		public UpdateSQL addField(String columnName, String opt, Object value) {
			if (value == null) {
				this.fields.put(columnName, String.valueOf(opt) + " NULL");
			} else if (value instanceof SQL.SelectSQL || value instanceof SQLPart || value instanceof Parameter) {
				this.fields.put(String.valueOf(columnName) + " " + opt, value);
				if (value instanceof SQL.SelectSQL) {
					SQL.SelectSQL ss = (SQL.SelectSQL) value;
					getRootComplexParams().putAll(ss.getRootComplexParams());
					addParameters(ss.getParameters());
					ss.parent = this;
				} else if (value instanceof Parameter) {
					Parameter p = (Parameter) value;
					addParameter(p.parameterName, p.parameterValue);
				}
			} else {
				this.fields.put(columnName, String.valueOf(opt) + " #{" + columnName + "}");
				addParameter(columnName, value);
			}
			return this;
		}

		public UpdateSQL addParameters(Map<String, Object> params) {
			_addParameters(params);
			return this;
		}

		public UpdateSQL addParameter(String name, Object value) {
			_addParameter(name, value);
			return this;
		}

		public UpdateSQL where(String where) {
			if (where != null && where.length() > 0){
				this.where.add(where);
			}
			return this;
		}

		public UpdateSQL where(String where, Map<String, Object> params) {
			if (where != null && where.length() > 0){
				this.where.add(where);
			}
			addParameters(params);
			return this;
		}

		public UpdateSQL where(String where, String paramsName1, Object paramValue1) {
			if (where != null && where.length() > 0){
				this.where.add(where);
			}
			addParameter(paramsName1, paramValue1);
			return this;
		}

		public UpdateSQL where(String where, String paramsName1, Object paramValue1, String paramsName2,
				Object paramValue2) {
			if (where != null && where.length() > 0){
				this.where.add(where);
			}
			addParameter(paramsName1, paramValue1);
			addParameter(paramsName2, paramValue2);
			return this;
		}

		public UpdateSQL where(String where, String paramsName1, Object paramValue1, String paramsName2,
				Object paramValue2, String paramsName3, Object paramValue3) {
			if (where != null && where.length() > 0){
				this.where.add(where);
			}
			addParameter(paramsName1, paramValue1);
			addParameter(paramsName2, paramValue2);
			addParameter(paramsName3, paramValue3);
			return this;
		}

		public String toString() {
			if (this.fields.isEmpty()){
				return "";
			}
			StringBuilder sql = new StringBuilder("UPDATE ");
			sql.append(this.tableName);
			sql.append(" SET ");
			for (Map.Entry<String, Object> field : this.fields.entrySet()){
				sql.append(field.getKey()).append(field.getValue().toString()).append(',');
			}
			sql.setLength(sql.length() - 1);
			if (!this.where.isEmpty()) {
				sql.append(" WHERE 1=1 ");
				for (String w : this.where){
					sql.append(" AND ").append(w);
				}
			}
			String temp = sql.toString();
			Map<String, Integer> cp = getRootComplexParams();
			SQL.ComplexTokenHandler handler1 = new SQL.ComplexTokenHandler("#{", "}", cp);
			GenericTokenParser parser1 = new GenericTokenParser("#{", "}", handler1);
			temp = parser1.parse(temp);
			SQL.ComplexTokenHandler handler2 = new SQL.ComplexTokenHandler("${", "}", cp);
			GenericTokenParser parser2 = new GenericTokenParser("${", "}", handler2);
			return parser2.parse(temp);
		}
	}

	public class InsertSQL extends SQLBase {
		String tableName;

		Map<String, Object> defValues;

		Set<String> columns;

		List<Map<String, Object>> values;

		SQL.SelectSQL select;

		boolean useReplace;

		Boolean useInsertAll;

		List<String> idFields;

		public InsertSQL() {
			this.defValues = new LinkedHashMap<>();
			this.columns = new HashSet<>();
			this.values = new ArrayList<>();
			this.select = null;
			this.useReplace = false;
			this.useInsertAll = null;
			this.idFields = new ArrayList<>();
		}

		public String getIdFields() {
			StringBuilder ids = new StringBuilder();
			for (String id : this.idFields){
				ids.append(id).append(',');
			}
			if (ids.length() > 0) {
				ids.setLength(ids.length() - 1);
				return ids.toString();
			}
			return "id";
		}

		public InsertSQL setIdFields(String... idColumns) {
			if (idColumns != null) {
				byte b;
				int i;
				String[] arrayOfString;
				for (i = (arrayOfString = idColumns).length, b = 0; b < i;) {
					String id = arrayOfString[b];
					if (id != null && id.length() > 0){
						this.idFields.add(id);
					}
					b++;
				}
			}
			return this;
		}

		public InsertSQL withReplace(boolean replace) {
			this.useReplace = replace;
			return this;
		}

		public InsertSQL useInsertAll(boolean useInsertAll) {
			this.useInsertAll = Boolean.valueOf(useInsertAll);
			return this;
		}

		public InsertSQL insert(String tableName, Map<String, Object> value) {
			this.tableName = tableName;
			this.select = null;
			if (value != null && !value.isEmpty()){
				addValue(value);
			}
			return this;
		}

		public InsertSQL insert(String tableName) {
			this.tableName = tableName;
			return this;
		}

		public InsertSQL insert(String tableName, SQL.SelectSQL values) {
			this.tableName = tableName;
			this.select = values;
			_addParameters(values.getParameters());
			getRootComplexParams().putAll(values.getRootComplexParams());
			values.parent = this;
			return this;
		}

		public InsertSQL insert(String tableName, String[] columns, SQL.SelectSQL values) {
			this.tableName = tableName;
			this.select = values;
			_addParameters(values.getParameters());
			getRootComplexParams().putAll(values.getRootComplexParams());
			values.parent = this;
			columns(columns);
			return this;
		}

		public InsertSQL columns(String... columns) {
			if (columns != null) {
				byte b;
				int i;
				String[] arrayOfString;
				for (i = (arrayOfString = columns).length, b = 0; b < i;) {
					String c = arrayOfString[b];
					if (c != null && c.length() > 0){
						this.columns.add(c);
					}
					b++;
				}
			}
			return this;
		}

		public InsertSQL values(Object... values) throws Exception {
			if (values != null) {
				String[] cols = this.columns.<String>toArray(new String[0]);
				Map<String, Object> val = new LinkedHashMap<>();
				if (cols.length == 0) {
					String postfix = "";
					if (this.values.size() > 0){
						postfix = "$" + this.values.size();
					}
					for (int i = 0; i < values.length; i++) {
						if (values[i] != null){
							if (values[i] instanceof SQL.SelectSQL || values[i] instanceof SQLPart
									|| values[i] instanceof Parameter) {
								val.put("`[" + i + "]", values[i]);
								if (values[i] instanceof SQL.SelectSQL) {
									SQL.SelectSQL ss = (SQL.SelectSQL) values[i];
									_addParameters(ss.getParameters());
									getRootComplexParams().putAll(ss.getRootComplexParams());
									ss.parent = this;
								} else if (values[i] instanceof Parameter) {
									Parameter p = (Parameter) values[i];
									_addParameter(p.parameterName, p.parameterValue);
								}
							} else {
								val.put("`[" + i + "]", "#{" + cols[i] + postfix + "}");
								_addParameter(String.valueOf(cols[i]) + postfix, values[i]);
							}
						}
					}
				} else {
					if (cols.length != values.length){
						throw new Exception("");
					}
					String postfix = "";
					if (this.values.size() > 0){
						postfix = "$" + this.values.size();
					}
					for (int i = 0; i < cols.length; i++) {
						if (values[i] != null){
							if (values[i] instanceof SQL.SelectSQL || values[i] instanceof SQLPart
									|| values[i] instanceof Parameter) {
								val.put(cols[i], values[i]);
								if (values[i] instanceof SQL.SelectSQL) {
									SQL.SelectSQL ss = (SQL.SelectSQL) values[i];
									_addParameters(ss.getParameters());
									getRootComplexParams().putAll(ss.getRootComplexParams());
									ss.parent = this;
								} else if (values[i] instanceof Parameter) {
									Parameter p = (Parameter) values[i];
									_addParameter(p.parameterName, p.parameterValue);
								}
							} else {
								val.put(cols[i], "#{" + cols[i] + postfix + "}");
								_addParameter(String.valueOf(cols[i]) + postfix, values[i]);
							}
						}
					}
				}
				this.values.add(val);
			}
			return this;
		}

		public InsertSQL resetColumns() {
			this.columns.clear();
			return this;
		}

		public InsertSQL addValue(Map<String, Object> value) {
			if (value != null && value.size() > 0) {
				Map<String, Object> val = new LinkedHashMap<>();
				String postfix = "";
				if (this.values.size() > 0){
					postfix = "$" + this.values.size();
				}
				for (Map.Entry<String, Object> v : value.entrySet()) {
					this.columns.add(v.getKey());
					Object obj = v.getValue();
					if (obj != null) {
						if (obj instanceof SQL.SelectSQL || obj instanceof SQLPart || obj instanceof Parameter) {
							val.put(v.getKey(), v.getValue());
							if (obj instanceof SQL.SelectSQL) {
								SQL.SelectSQL ss = (SQL.SelectSQL) obj;
								_addParameters(ss.getParameters());
								getRootComplexParams().putAll(ss.getRootComplexParams());
								ss.parent = this;
								continue;
							}
							if (obj instanceof Parameter) {
								Parameter p = (Parameter) obj;
								_addParameter(p.parameterName, p.parameterValue);
							}
							continue;
						}
						val.put(v.getKey(), "#{" + (String) v.getKey() + postfix + "}");
						_addParameter(String.valueOf(v.getKey()) + postfix, v.getValue());
					}
				}
				this.values.add(val);
			}
			return this;
		}

		public InsertSQL addValuePart(String columnName, Object value, int rowIndex) {
			Map<String, Object> val;
			this.columns.add(columnName);
			if (rowIndex < 0 || rowIndex >= this.values.size()) {
				val = new LinkedHashMap<>();
				this.values.add(val);
			} else {
				val = this.values.get(rowIndex);
			}
			if (value != null){
				if (value instanceof SQL.SelectSQL) {
					val.put(columnName, value);
					SQL.SelectSQL ss = (SQL.SelectSQL) value;
					_addParameters(ss.getParameters());
					getRootComplexParams().putAll(ss.getRootComplexParams());
					ss.parent = this;
				} else {
					String postfix = "";
					if (this.values.size() > 0){
						postfix = "$" + this.values.size();
					}
					if (value instanceof SQLPart) {
						val.put(columnName, value);
					} else if (value instanceof SQL.SelectSQL) {
						val.put(columnName, value);
						SQL.SelectSQL ss = (SQL.SelectSQL) value;
						_addParameters(ss.getParameters());
						getRootComplexParams().putAll(ss.getRootComplexParams());
						ss.parent = this;
					} else if (value instanceof Parameter) {
						val.put(columnName, value);
						Parameter p = (Parameter) value;
						_addParameter(p.parameterName, p.parameterValue);
					} else {
						val.put(columnName, "#{" + columnName + postfix + "}");
						_addParameter(String.valueOf(columnName) + postfix, value);
					}
				}
			}
			return this;
		}

		public InsertSQL addValuePart(String columnName, Object value) {
			return addValuePart(columnName, value, -1);
		}

		public InsertSQL addValuePart(int columnIndex, Object value, int rowIndex) {
			return addValuePart("`[" + columnIndex + "]", value, rowIndex);
		}

		public InsertSQL addValuePart(int columnIndex, Object value) {
			return addValuePart(columnIndex, value, -1);
		}

		public InsertSQL addValues(SQL.SelectSQL values) {
			this.select = values;
			_addParameters(values.getParameters());
			getRootComplexParams().putAll(values.getRootComplexParams());
			values.parent = this;
			return this;
		}

		public InsertSQL defaultValues(Map<String, Object> defValues) {
			if (defValues != null){
				for (Map.Entry<String, Object> v : defValues.entrySet()){
					this.defValues.put(v.getKey(), v.getValue());
				}
			}
			return this;
		}

		public InsertSQL defaultValue(String columnName, Object defValues) {
			this.defValues.put(columnName, defValues);
			return this;
		}

		public InsertSQL defaultValue(Integer columnIndex, Object defValues) {
			this.defValues.put("`[" + columnIndex + "]", defValues);
			return this;
		}

		int getColumnCount() {
			int count = 0;
			for (Map<String, Object> v : this.values) {
				if (v.size() > count){
					count = v.size();
				}
			}
			return count;
		}

		public String toString() {
			StringBuilder sql = new StringBuilder();
			if (this.useInsertAll == null){
				this.useInsertAll = Boolean.valueOf(false);
			}
			if (this.useInsertAll.booleanValue()) {
				sql.append("INSERT ALL ");
				for (Map<String, Object> val : this.values) {
					sql.append(" INTO ").append(this.tableName);
					if (!this.columns.isEmpty()) {
						sql.append('(');
						for (String col : this.columns){
							sql.append(col).append(',');
						}
						sql.setLength(sql.length() - 1);
						sql.append(')');
					}
					sql.append(" VALUES(");
					if (this.columns.isEmpty()) {
						int count = getColumnCount();
						for (int i = 0; i < count; i++) {
							String colName = "`[" + i + "]";
							Object v = val.get(colName);
							if (v == null){
								v = this.defValues.get(colName);
							}
							if (v == null) {
								sql.append("NULL,");
							} else {
								sql.append(v.toString()).append(",");
							}
						}
						sql.setLength(sql.length() - 1);
					} else {
						for (String colName : this.columns) {
							Object v = val.get(colName);
							if (v == null){
								v = this.defValues.get(colName);
							}
							if (v == null) {
								sql.append("NULL,");
								continue;
							}
							sql.append(v.toString()).append(",");
						}
						sql.setLength(sql.length() - 1);
					}
					sql.append(") \n");
				}
				sql.append("select 1 from sys.dual");
			} else {
				if (this.useReplace) {
					sql.append("REPLACE INTO ");
				} else {
					sql.append("INSERT INTO ");
				}
				sql.append(this.tableName);
				if (!this.columns.isEmpty()) {
					sql.append('(');
					for (String col : this.columns){
						sql.append(col).append(',');
					}
					sql.setLength(sql.length() - 1);
					sql.append(')');
				}
				if (this.values.isEmpty()) {
					sql.append(" ");
					sql.append(this.select.toString());
				} else {
					sql.append(" VALUES ");
					for (Map<String, Object> val : this.values) {
						sql.append('(');
						if (this.columns.isEmpty()) {
							int count = getColumnCount();
							for (int i = 0; i < count; i++) {
								String colName = "`[" + i + "]";
								Object v = val.get(colName);
								if (v == null){
									v = this.defValues.get(colName);
								}
								if (v == null) {
									sql.append("NULL,");
								} else {
									sql.append(v.toString()).append(",");
								}
							}
							sql.setLength(sql.length() - 1);
						} else {
							for (String colName : this.columns) {
								Object v = val.get(colName);
								if (v == null){
									v = this.defValues.get(colName);
								}
								if (v == null) {
									sql.append("NULL,");
									continue;
								}
								sql.append(v.toString()).append(",");
							}
							sql.setLength(sql.length() - 1);
						}
						sql.append("),");
					}
					sql.setLength(sql.length() - 1);
				}
			}
			String temp = sql.toString();
			Map<String, Integer> cp = getRootComplexParams();
			SQL.ComplexTokenHandler handler1 = new SQL.ComplexTokenHandler("#{", "}", cp);
			GenericTokenParser parser1 = new GenericTokenParser("#{", "}", handler1);
			temp = parser1.parse(temp);
			SQL.ComplexTokenHandler handler2 = new SQL.ComplexTokenHandler("${", "}", cp);
			GenericTokenParser parser2 = new GenericTokenParser("${", "}", handler2);
			return parser2.parse(temp);
		}
	}

	public class DeleteSQL extends SQLBase {
		List<String> filter;

		String tableName;

		public DeleteSQL() {
			this.filter = new ArrayList<>();
		}

		public DeleteSQL delete(String tableName, String where, Map<String, Object> params) {
			this.tableName = tableName;
			if (where != null && where.length() > 0){
				this.filter.add(where);
			}
			addParameters(params);
			return this;
		}

		public DeleteSQL delete(String tableName) {
			this.tableName = tableName;
			return this;
		}

		public DeleteSQL where(String where, Map<String, Object> params) {
			if (where != null && where.length() > 0){
				this.filter.add(where);
			}
			addParameters(params);
			return this;
		}

		public DeleteSQL where(String where) {
			if (where != null && where.length() > 0){
				this.filter.add(where);
			}
			return this;
		}

		public DeleteSQL addParameter(String paramName, Object paramValue) {
			_addParameter(paramName, paramValue);
			return this;
		}

		public DeleteSQL addParameters(Map<String, Object> params) {
			_addParameters(params);
			return this;
		}

		public String toString() {
			StringBuilder sql = new StringBuilder("DELETE FROM ");
			sql.append(this.tableName);
			sql.append("  WHERE 1=1 ");
			for (String where : this.filter){
				sql.append(" AND ").append(where);
			}
			String temp = sql.toString();
			Map<String, Integer> cp = getRootComplexParams();
			SQL.ComplexTokenHandler handler1 = new SQL.ComplexTokenHandler("#{", "}", cp);
			GenericTokenParser parser1 = new GenericTokenParser("#{", "}", handler1);
			temp = parser1.parse(temp);
			SQL.ComplexTokenHandler handler2 = new SQL.ComplexTokenHandler("${", "}", cp);
			GenericTokenParser parser2 = new GenericTokenParser("${", "}", handler2);
			return parser2.parse(temp);
		}
	}

	public class ExecuteSQL {
		String procName;

		Map<String, Parameter> params;

		Map<String, Object> parameter;

		public ExecuteSQL() {
			this.params = new LinkedHashMap<>();
			this.parameter = null;
		}

		public ExecuteSQL exec(String procName, Map<String, Object> params) {
			this.procName = procName;
			addParams(params);
			return this;
		}

		void addParams(Map<String, Object> params) {
			if (params != null){
				for (Map.Entry<String, Object> p : params.entrySet()){
					this.params.put(p.getKey(), new Parameter(p.getKey(), p.getValue(), null, null, null, null, null));
				}
			}
		}

		public ExecuteSQL exec(String procName) {
			this.procName = procName;
			return this;
		}

		public ExecuteSQL addParameters(Map<String, Object> params) {
			addParams(params);
			return this;
		}

		public ExecuteSQL addParameter(String parameterName, Object parameterValue) {
			this.params.put(parameterName, new Parameter(parameterName, parameterValue, null, null, null, null, null));
			return this;
		}

		public ExecuteSQL addParameter(String parameterName, Object parameterValue, DirectionModel mode) {
			this.params.put(parameterName, new Parameter(parameterName, parameterValue, null, null, null, null, null));
			return this;
		}

		public ExecuteSQL addParameter(String parameterName, Object parameterValue, DirectionModel mode,
				String jdbcType) {
			this.params.put(parameterName,
					new Parameter(parameterName, parameterValue, mode, jdbcType, null, null, null));
			return this;
		}

		public ExecuteSQL addParameter(String parameterName, Object parameterValue, DirectionModel mode,
				String jdbcType, String javaType) {
			this.params.put(parameterName,
					new Parameter(parameterName, parameterValue, mode, jdbcType, javaType, null, null));
			return this;
		}

		public ExecuteSQL addParameter(String parameterName, Object parameterValue, DirectionModel mode,
				String jdbcType, String javaType, Integer numericScale) {
			this.params.put(parameterName,
					new Parameter(parameterName, parameterValue, mode, jdbcType, javaType, numericScale, null));
			return this;
		}

		public ExecuteSQL addParameter(String parameterName, Object parameterValue, DirectionModel mode,
				String jdbcType, String javaType, Integer numericScale, String typeHandler) {
			this.params.put(parameterName,
					new Parameter(parameterName, parameterValue, mode, jdbcType, javaType, numericScale, typeHandler));
			return this;
		}

		public Map<String, Object> getParameters(boolean generateNew) {
			if (this.parameter == null || generateNew) {
				Map<String, Object> p = new LinkedHashMap<>();
				for (Parameter ep : this.params.values()) {
					if (!(ep.parameterValue instanceof SQLPart)){
						p.put(ep.parameterName, ep.parameterValue);
					}
				}
				this.parameter = p;
			}
			return this.parameter;
		}

		public Map<String, Object> getParameters() {
			return getParameters(false);
		}

		public String toString() {
			StringBuilder sql = new StringBuilder();
			sql.append('{');
			Parameter returnP = null;
			for (Map.Entry<String, Parameter> ep : this.params.entrySet()) {
				if (((Parameter) ep.getValue()).mode == DirectionModel.RETURN) {
					returnP = ep.getValue();
					break;
				}
			}
			if (returnP != null) {
				returnP.toString(sql, DirectionModel.OUT);
				sql.append("=");
			}
			sql.append("call ").append(this.procName).append("( ");
			for (Map.Entry<String, Parameter> ep : this.params.entrySet()) {
				((Parameter) ep.getValue()).toString(sql);
				sql.append(',');
			}
			sql.setLength(sql.length() - 1);
			sql.append(")}");
			return sql.toString();
		}
	}

	class ComplexTokenHandler implements TokenHandler {
		Map<String, Integer> paramList;

		String openToken;

		String closeToken;

		public ComplexTokenHandler(String openToken, String closeToken, Map<String, Integer> paramList) {
			this.openToken = openToken;
			this.closeToken = closeToken;
			this.paramList = paramList;
		}

		public String handleToken(String content) {
			Map<String, String> propertiesMap = new SQL.ParameterExpression(content);
			String property = propertiesMap.get("property");
			if (property != null && this.paramList.containsKey(property)) {
				StringBuffer buffer = new StringBuffer();
				String separator = ",";
				if (propertiesMap.containsKey("separator")){
					separator = propertiesMap.get("separator");
				}
				int count = ((Integer) this.paramList.get(property)).intValue();
				for (int index = 0; index < count; index++) {
					buffer.append(this.openToken).append("__").append(property).append('_').append(index).append(',');
					for (Map.Entry<String, String> entry : propertiesMap.entrySet()) {
						String key = entry.getKey();
						if ("property".equals(key) || "separator".equals(key)){
							continue;
						}
						buffer.append(key).append('=').append(entry.getValue()).append(',');
					}
					buffer.setLength(buffer.length() - 1);
					buffer.append(this.closeToken).append(separator);
				}
				buffer.setLength(buffer.length() - separator.length());
				return buffer.toString();
			}
			return String.valueOf(this.openToken) + content + this.closeToken;
		}
	}

	public class ParameterExpression extends HashMap<String, String> {
		private static final long serialVersionUID = -2417552199605158680L;

		public ParameterExpression(String expression) {
			parse(expression);
		}

		private void parse(String expression) {
			int p = skipWS(expression, 0);
			if (expression.charAt(p) == '(') {
				expression(expression, p + 1);
			} else {
				property(expression, p);
			}
		}

		private void expression(String expression, int left) {
			int match = 1;
			int right = left + 1;
			while (match > 0) {
				if (expression.charAt(right) == ')') {
					match--;
				} else if (expression.charAt(right) == '(') {
					match++;
				}
				right++;
			}
			put("expression", expression.substring(left, right - 1));
			jdbcTypeOpt(expression, right);
		}

		private void property(String expression, int left) {
			if (left < expression.length()) {
				int right = skipUntil(expression, left, ",:");
				put("property", trimmedStr(expression, left, right));
				jdbcTypeOpt(expression, right);
			}
		}

		private int skipWS(String expression, int p) {
			for (int i = p; i < expression.length(); i++) {
				if (expression.charAt(i) > ' '){
					return i;
				}
			}
			return expression.length();
		}

		private int skipUntil(String expression, int p, String endChars) {
			for (int i = p; i < expression.length(); i++) {
				char c = expression.charAt(i);
				if (endChars.indexOf(c) > -1){
					return i;
				}
			}
			return expression.length();
		}

		private void jdbcTypeOpt(String expression, int p) {
			p = skipWS(expression, p);
			if (p < expression.length()){
				if (expression.charAt(p) == ':') {
					jdbcType(expression, p + 1);
				} else if (expression.charAt(p) == ',') {
					option(expression, p + 1);
				} else {
					throw new BuilderException("Parsing error in {" + expression + "} in position " + p);
				}
			}
		}

		private void jdbcType(String expression, int p) {
			int left = skipWS(expression, p);
			int right = skipUntil(expression, left, ",");
			if (right > left) {
				put("jdbcType", trimmedStr(expression, left, right));
			} else {
				throw new BuilderException("Parsing error in {" + expression + "} in position " + p);
			}
			option(expression, right + 1);
		}

		private void option(String expression, int p) {
			int left = skipWS(expression, p);
			if (left < expression.length()) {
				int right = skipUntil(expression, left, "=");
				String name = trimmedStr(expression, left, right);
				left = right + 1;
				if (expression.charAt(left) == '"') {
					right = skipUntil(expression, left + 1, "\"");
					while (right + 1 < expression.length() && expression.charAt(right + 1) == '"') {
						right += 2;
						right = skipUntil(expression, right, "\"");
					}
				} else if (expression.charAt(left) == '\'') {
					right = skipUntil(expression, left + 1, "'");
					while (right + 1 < expression.length() && expression.charAt(right + 1) == '\'') {
						right += 2;
						right = skipUntil(expression, right, "'");
					}
				}
				right = skipUntil(expression, right, ",");
				String value = trimmedStr(expression, left, right);
				put(name, value);
				option(expression, right + 1);
			}
		}

		private String trimmedStr(String str, int start, int end) {
			while (str.charAt(start) <= ' '){
				start++;
			}
			while (str.charAt(end - 1) <= ' '){
				end--;

			}
			if (str.charAt(start) == '"' && str.charAt(end - 1) == '"') {
				start++;
				end--;
				if (start >= end){
					return "";
				}
				return str.substring(start, end).replace("\"\"", "\"");
			}
			if (str.charAt(start) == '\'' || str.charAt(end - 1) == '\'') {
				start++;
				end--;
				if (start >= end){
					return "";
				}
				return str.substring(start, end).replace("''", "'");
			}
			return (start >= end) ? "" : str.substring(start, end);
		}
	}
}
